package org.jeecg.modules.business.mapper;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;

import cn.hutool.core.date.DateTime;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.business.entity.BusHotel;
import org.jeecg.modules.business.entity.BusRoomPayType;

/**
 * @Description: bus_hotel
 * @Author: jeecg-boot
 * @Date:   2023-02-14
 * @Version: V1.0
 */
public interface BusHotelMapper extends BaseMapper<BusHotel> {

    /**
     * 可入住酒店列表
     * @param page
     * @param tenantId 租户id
     * @param keyWord 关键字
     * @param sort 0推荐排序 1 距离优先 2低价优先 3评分优先 4评论数优先
     * @return
     */
    @Select("<script>SELECT h.*, ROUND(6378.138*2*ASIN(SQRT(power(SIN((${lat}*PI()/180-(h.lat)*PI()/180)/2),2)+COS(${lat}*PI()/180)*COS((h.lat)*PI()/180)*power(SIN((${lng}*PI()/180-(h.lng)*PI()/180)/2),2)))*1000,2)  AS distance,ifnull(c.commentNum,0) as commentNum,ifnull(c.score,0) as score,ifnull(market_price,0) as min_price FROM bus_hotel_info h\n" +
            "left join (select hotel_id,count(0) as commentNum,AVG(score) as score from ces_order_comment where comment_type=1 group by hotel_id) c\n" +
            "on c.hotel_id=h.id\n" +
            "left join (select hotel_id,min(market_price) as market_price from ces_room_layout where invalid=0 group by hotel_id) l\n" +
            "on l.hotel_id=h.id\n" +
            "where h.del_flag=0 and h.`status`=1 and h.check_status=1\n" +
            "<if test='tenantId != null and tenantId !=\"\"'> and h.tenant_id = '${tenantId}' </if>" +
            "<if test='city != null and city !=\"\"'> and h.address LIKE concat(concat('%',#{city}),'%') </if>" +
            "<if test='keyWord != null and keyWord !=\"\"'> and (h.address LIKE concat(concat('%',#{keyWord}),'%') or h.name LIKE concat(concat('%',#{keyWord}),'%')) </if>" +
            "<if test='sort != null and sort ==0'> order by h.create_time desc </if>" +
            "<if test='sort != null and sort ==1'> order by distance asc </if>" +
            "<if test='sort != null and sort ==2'> order by min_price asc </if>" +
            "<if test='sort != null and sort ==3'> order by score desc </if>" +
            "<if test='sort != null and sort ==4'> order by commentNum desc </if>" +
            "</script>")
    public List<BusHotel> pageList(Page<BusHotel> page,@Param("tenantId") String tenantId, @Param("city") String city, @Param("keyWord") String keyWord, @Param("sort") Integer sort, @Param("lat") BigDecimal lat, @Param("lng") BigDecimal lng);

}


